import akshare as ak
import pandas as pd
from datetime import datetime, timedelta


# 1. 获取过去一年日期范围
# 数据从13年10月18日开始有铁矿石
# end_date = datetime.now().strftime("%Y%m%d")
# start_date = (datetime.now() - timedelta(days=365)).strftime("%Y%m%d")


# 2. 获取焦煤主连数据
def get_main_contract_data(symbol):
    # 默认从开头取到现在
    df = ak.futures_main_sina(symbol=symbol)
    df['date'] = pd.to_datetime(df['日期']).dt.strftime('%Y-%m-%d')
    # df = df[(df['date'] >= start) & (df['date'] <= end)]
    return df[['date', '开盘价', '收盘价']].rename(columns={'开盘价': 'open', '收盘价': 'close'})


# 获取黄金(AU0)和白银(AG0)数据
au_data = get_main_contract_data("AU0")
ag_data = get_main_contract_data("AG0")

# 3. 合并数据并计算比值
# 合并黄金白银
merged_data = pd.merge(au_data, ag_data, on='date', suffixes=('_AU', '_AG'))

# 计算黄精、白银套利
merged_data['AU/AG_Open_Ratio'] = merged_data['open_AU'] / merged_data['open_AG']
merged_data['AU/AG_Close_Ratio'] = merged_data['close_AU'] / merged_data['close_AG']
merged_data['AU/AG_Ratio_Diff'] = merged_data['AU/AG_Close_Ratio'] - merged_data['AU/AG_Open_Ratio']

# 4. 导出Excel（优化列名与格式）
output_columns = {
    'date': '日期',
    'open_AU': '黄金开盘',
    'close_AU': '黄金收盘',
    'open_AG': '白银开盘',
    'close_AG': '白银收盘',

    'AU/AG_Open_Ratio': 'AU/AG开盘比值',
    'AU/AG_Close_Ratio': 'AU/AG收盘比值',
    'AU/AG_Ratio_Diff': 'AU/AG收开差',
}
merged_data.rename(columns=output_columns, inplace=True)
merged_data.to_excel("黄金白银比值.xlsx", index=False, engine='openpyxl')
